const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 获取管理员统计数据
router.get('/statistics', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }

    // 获取用户总数
    const [userCountResult] = await pool.query('SELECT COUNT(*) as count FROM users');
    const userCount = userCountResult[0].count;

    // 获取学生总数
    const [studentCountResult] = await pool.query('SELECT COUNT(*) as count FROM users WHERE role = "student"');
    const studentCount = studentCountResult[0].count;

    // 获取教师总数
    const [teacherCountResult] = await pool.query('SELECT COUNT(*) as count FROM users WHERE role = "teacher"');
    const teacherCount = teacherCountResult[0].count;

    // 获取辅导员总数
    const [counselorCountResult] = await pool.query('SELECT COUNT(*) as count FROM users WHERE role = "counselor"');
    const counselorCount = counselorCountResult[0].count;

    // 获取班级总数
    const [classCountResult] = await pool.query('SELECT COUNT(*) as count FROM classes');
    const classCount = classCountResult[0].count;

    // 获取课程总数
    const [courseCountResult] = await pool.query('SELECT COUNT(*) as count FROM courses');
    const courseCount = courseCountResult[0].count;

    // 获取考试总数
    const [examCountResult] = await pool.query('SELECT COUNT(*) as count FROM exams');
    const examCount = examCountResult[0].count;

    // 获取请假申请总数
    const [leaveCountResult] = await pool.query('SELECT COUNT(*) as count FROM leaves');
    const leaveCount = leaveCountResult[0].count;

    // 获取待审批的请假申请数量
    const [pendingLeaveCountResult] = await pool.query('SELECT COUNT(*) as count FROM leaves WHERE status = "pending"');
    const pendingLeaveCount = pendingLeaveCountResult[0].count;

    // 获取今日登录用户数
    const today = new Date().toISOString().split('T')[0];
    const [todayLoginCountResult] = await pool.query(
      'SELECT COUNT(DISTINCT userId) as count FROM logs WHERE action = "login" AND DATE(timestamp) = ?',
      [today]
    );
    const todayLoginCount = todayLoginCountResult[0].count;

    res.json({
      userCount,
      studentCount,
      teacherCount,
      counselorCount,
      classCount,
      courseCount,
      examCount,
      leaveCount,
      pendingLeaveCount,
      todayLoginCount
    });
  } catch (error) {
    console.error('获取管理员统计数据失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取最近活动
router.get('/activities', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }

    // 获取最近的系统活动日志
    const [activities] = await pool.query(`
      SELECT 
        logs.id,
        logs.userId,
        logs.userName,
        logs.userRole,
        logs.action,
        logs.target,
        logs.targetId,
        logs.content,
        logs.ip,
        logs.timestamp
      FROM logs
      ORDER BY logs.timestamp DESC
      LIMIT 10
    `);

    res.json(activities);
  } catch (error) {
    console.error('获取最近活动失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取系统概览数据
router.get('/overview', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }

    // 获取最近7天的登录统计
    const [loginStats] = await pool.query(`
      SELECT 
        DATE(timestamp) as date,
        COUNT(DISTINCT userId) as count
      FROM logs
      WHERE action = 'login' AND timestamp >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
      GROUP BY DATE(timestamp)
      ORDER BY date ASC
    `);

    // 获取各学院学生人数统计
    const [departmentStats] = await pool.query(`
      SELECT 
        department,
        COUNT(*) as count
      FROM users
      WHERE role = 'student'
      GROUP BY department
      ORDER BY count DESC
    `);

    // 获取各班级学生人数
    const [classStats] = await pool.query(`
      SELECT 
        c.className,
        COUNT(u.id) as count
      FROM classes c
      LEFT JOIN users u ON c.id = u.classId AND u.role = 'student'
      GROUP BY c.id
      ORDER BY count DESC
    `);

    res.json({
      loginStats,
      departmentStats,
      classStats
    });
  } catch (error) {
    console.error('获取系统概览数据失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取待处理事项
router.get('/pending-tasks', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }

    // 获取待审批的请假申请
    const [pendingLeaves] = await pool.query(`
      SELECT 
        l.id,
        l.studentId,
        u.name as studentName,
        l.type,
        l.startDate,
        l.endDate,
        l.days,
        l.reason,
        l.applyTime
      FROM leaves l
      JOIN users u ON l.studentId = u.id
      WHERE l.status = 'pending'
      ORDER BY l.applyTime ASC
    `);

    res.json({
      pendingLeaves
    });
  } catch (error) {
    console.error('获取待处理事项失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取班级列表
router.get('/classes', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 获取所有班级
    const [classes] = await pool.query(`
      SELECT id, className as name, department, grade 
      FROM classes 
      ORDER BY department, grade, className
    `);
    
    res.json(classes);
  } catch (error) {
    console.error('获取班级列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 检查考勤数据
router.get('/check-attendance', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 获取考勤记录总数
    const [countResult] = await pool.query('SELECT COUNT(*) as count FROM attendance');
    const count = countResult[0].count;
    
    // 获取最近10条考勤记录
    const [recentRecords] = await pool.query(`
      SELECT 
        a.id,
        a.studentId,
        a.courseId,
        a.classId,
        a.date,
        a.status,
        a.checkInTime,
        a.checkOutTime
      FROM attendance a
      ORDER BY a.id DESC
      LIMIT 10
    `);
    
    // 获取不同日期的记录数
    const [dateStats] = await pool.query(`
      SELECT 
        a.date,
        COUNT(*) as count
      FROM attendance a
      GROUP BY a.date
      ORDER BY a.date DESC
      LIMIT 10
    `);
    
    res.json({
      totalRecords: count,
      recentRecords,
      dateStats
    });
  } catch (error) {
    console.error('检查考勤数据失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取考勤数据
router.get('/attendance', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { date, classId } = req.query;
    
    console.log('查询参数:', { date, classId });
    
    // 构建查询条件
    let whereClause = '';
    const params = [];
    
    if (date) {
      // 使用DATE函数确保日期格式匹配
      whereClause += ' WHERE DATE(a.date) = DATE(?)';
      params.push(date);
    }
    
    if (classId && classId !== '0') {
      whereClause += whereClause ? ' AND a.classId = ?' : ' WHERE a.classId = ?';
      params.push(classId);
    }
    
    // 如果没有条件，获取最近的记录
    if (!whereClause) {
      whereClause = ' ORDER BY a.date DESC, a.checkInTime DESC LIMIT 100';
    } else {
      whereClause += ' ORDER BY a.date DESC, a.checkInTime DESC';
    }
    
    console.log('SQL查询条件:', whereClause, params);
    
    // 获取考勤记录
    const recordsQuery = `
      SELECT 
        a.id,
        a.studentId,
        u.name as studentName,
        u.userId as studentNumber,
        c.className,
        a.date,
        a.checkInTime,
        a.checkOutTime,
        a.status,
        a.location,
        a.remark
      FROM attendance a
      JOIN users u ON a.studentId = u.id
      LEFT JOIN classes c ON a.classId = c.id
      ${whereClause}
    `;
    
    console.log('完整SQL查询:', recordsQuery);
    
    const [records] = await pool.query(recordsQuery, params);
    
    console.log('查询到的记录数:', records.length);
    
    // 获取统计数据
    let statsWhereClause = whereClause;
    if (statsWhereClause.includes('ORDER BY')) {
      statsWhereClause = statsWhereClause.substring(0, statsWhereClause.indexOf('ORDER BY'));
    }
    if (statsWhereClause.includes('LIMIT')) {
      statsWhereClause = statsWhereClause.substring(0, statsWhereClause.indexOf('LIMIT'));
    }
    
    const statsQuery = `
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
        SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
        SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent
      FROM attendance a
      ${statsWhereClause}
    `;
    
    const [statsResult] = await pool.query(statsQuery, params);
    
    console.log('统计结果:', statsResult[0]);
    
    // 确保统计值不为null
    const statistics = {
      total: statsResult[0].total || 0,
      present: statsResult[0].present || 0,
      late: statsResult[0].late || 0,
      absent: statsResult[0].absent || 0
    };
    
    res.json({
      records,
      statistics
    });
  } catch (error) {
    console.error('获取考勤数据失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 导出考勤统计数据
router.get('/attendance/export', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { startDate, endDate, classId, groupBy } = req.query;
    
    // 构建查询条件
    let whereClause = '';
    const params = [];
    
    if (startDate && endDate) {
      whereClause += ' WHERE a.date BETWEEN ? AND ?';
      params.push(startDate, endDate);
    } else if (startDate) {
      whereClause += ' WHERE a.date >= ?';
      params.push(startDate);
    } else if (endDate) {
      whereClause += ' WHERE a.date <= ?';
      params.push(endDate);
    }
    
    if (classId && classId !== '0') {
      whereClause += whereClause ? ' AND a.classId = ?' : ' WHERE a.classId = ?';
      params.push(classId);
      
      // 获取班级名称
      const [classResult] = await pool.query('SELECT className FROM classes WHERE id = ?', [classId]);
      if (classResult.length > 0) {
        fileName = `考勤统计_${classResult[0].className}`;
      } else {
        fileName = `考勤统计_班级${classId}`;
      }
    } else {
      fileName = '考勤统计_全部班级';
    }
    
    fileName += `_${startDate}至${endDate}`;
    
    // 根据groupBy参数决定导出哪种数据
    let exportData = [];
    let worksheetName = '';
    let columns = [];
    
    if (groupBy === 'course') {
      // 导出课程考勤统计
      worksheetName = '课程考勤统计';
      
      const [courseStats] = await pool.query(`
        SELECT 
          c.id as courseId,
          c.name as courseName,
          COUNT(*) as total,
          SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
          SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
          SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
          SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leave\`,
          SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) as early,
          ROUND(SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as rate
        FROM attendance a
        JOIN courses c ON a.courseId = c.id
        ${whereClause}
        GROUP BY c.id, c.name
        ORDER BY c.name
      `, params);
      
      exportData = courseStats.map(item => ({
        courseName: item.courseName,
        total: item.total,
        present: item.present,
        late: item.late,
        absent: item.absent,
        leave: item.leave,
        early: item.early,
        rate: item.rate + '%'
      }));
      
      columns = [
        { header: '课程名称', key: 'courseName', width: 20 },
        { header: '总人次', key: 'total', width: 10 },
        { header: '出勤人次', key: 'present', width: 10 },
        { header: '迟到人次', key: 'late', width: 10 },
        { header: '缺勤人次', key: 'absent', width: 10 },
        { header: '请假人次', key: 'leave', width: 10 },
        { header: '早退人次', key: 'early', width: 10 },
        { header: '出勤率', key: 'rate', width: 10 }
      ];
    } else if (groupBy === 'student') {
      // 导出学生考勤统计
      worksheetName = '学生考勤统计';
      
      const [studentStats] = await pool.query(`
        SELECT 
          a.studentId,
          u.name as studentName,
          u.userId as studentNumber,
          c.className,
          COUNT(*) as total,
          SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
          SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
          SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
          SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leave\`,
          SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) as early,
          ROUND(SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as rate
        FROM attendance a
        JOIN users u ON a.studentId = u.id
        LEFT JOIN classes c ON a.classId = c.id
        ${whereClause}
        GROUP BY a.studentId, u.name, u.userId, c.className
        ORDER BY c.className, u.name
      `, params);
      
      exportData = studentStats.map(item => ({
        studentName: item.studentName,
        studentNumber: item.studentNumber,
        className: item.className || '未分配',
        total: item.total,
        present: item.present,
        late: item.late,
        absent: item.absent,
        leave: item.leave,
        early: item.early,
        rate: item.rate + '%'
      }));
      
      columns = [
        { header: '学生姓名', key: 'studentName', width: 15 },
        { header: '学号', key: 'studentNumber', width: 15 },
        { header: '班级', key: 'className', width: 20 },
        { header: '总课次', key: 'total', width: 10 },
        { header: '出勤次数', key: 'present', width: 10 },
        { header: '迟到次数', key: 'late', width: 10 },
        { header: '缺勤次数', key: 'absent', width: 10 },
        { header: '请假次数', key: 'leave', width: 10 },
        { header: '早退次数', key: 'early', width: 10 },
        { header: '出勤率', key: 'rate', width: 10 }
      ];
    } else {
      // 默认导出详细考勤记录
      worksheetName = '考勤详细记录';
      
      const [records] = await pool.query(`
        SELECT 
          u.name as studentName,
          u.userId as studentNumber,
          cl.className,
          c.name as courseName,
          a.date,
          a.checkInTime,
          a.checkOutTime,
          a.status,
          a.location,
          a.remark
        FROM attendance a
        JOIN users u ON a.studentId = u.id
        LEFT JOIN classes cl ON a.classId = cl.id
        JOIN courses c ON a.courseId = c.id
        ${whereClause}
        ORDER BY a.date DESC, cl.className, u.name
      `, params);
      
      exportData = records.map(item => {
        // 格式化状态
        let status = '';
        switch(item.status) {
          case 'present': status = '出勤'; break;
          case 'absent': status = '缺勤'; break;
          case 'late': status = '迟到'; break;
          case 'leave': status = '请假'; break;
          case 'early': status = '早退'; break;
          default: status = item.status;
        }
        
        // 格式化位置信息
        let location = item.location;
        try {
          if (item.location && item.location.startsWith('{')) {
            const locationObj = JSON.parse(item.location);
            location = locationObj.address || locationObj.latitude + ',' + locationObj.longitude;
          }
        } catch (e) {
          console.error('解析位置信息失败:', e);
        }
        
        return {
          studentName: item.studentName,
          studentNumber: item.studentNumber,
          className: item.className || '未分配',
          courseName: item.courseName,
          date: item.date,
          checkInTime: item.checkInTime ? new Date(item.checkInTime).toLocaleString() : '',
          checkOutTime: item.checkOutTime ? new Date(item.checkOutTime).toLocaleString() : '',
          status: status,
          location: location,
          remark: item.remark || ''
        };
      });
      
      columns = [
        { header: '学生姓名', key: 'studentName', width: 15 },
        { header: '学号', key: 'studentNumber', width: 15 },
        { header: '班级', key: 'className', width: 20 },
        { header: '课程', key: 'courseName', width: 20 },
        { header: '日期', key: 'date', width: 12 },
        { header: '签到时间', key: 'checkInTime', width: 20 },
        { header: '签退时间', key: 'checkOutTime', width: 20 },
        { header: '状态', key: 'status', width: 10 },
        { header: '位置', key: 'location', width: 30 },
        { header: '备注', key: 'remark', width: 20 }
      ];
    }
    
    // 创建Excel文件
    const Excel = require('exceljs');
    const path = require('path');
    const fs = require('fs');
    
    fileName += `.xlsx`;
    const filePath = path.join(__dirname, '../public/exports', fileName);
    
    // 确保目录存在
    if (!fs.existsSync(path.join(__dirname, '../public/exports'))) {
      fs.mkdirSync(path.join(__dirname, '../public/exports'), { recursive: true });
    }
    
    // 创建工作簿和工作表
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet(worksheetName);
    
    // 设置列
    worksheet.columns = columns;
    
    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    
    // 添加数据
    worksheet.addRows(exportData);
    
    // 保存文件
    await workbook.xlsx.writeFile(filePath);
    
    // 记录导出操作
    await pool.query(`
      INSERT INTO logs (userId, userName, userRole, action, target, content, ip)
      VALUES (?, ?, ?, 'export', 'attendance', ?, ?)
    `, [req.user.id, req.user.name || '管理员', req.user.role, `导出考勤统计(${exportData.length}条)`, req.ip]);
    
    // 设置响应头，直接下载文件
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=${encodeURIComponent(fileName)}`);
    
    // 发送文件
    fs.createReadStream(filePath).pipe(res);
    
  } catch (error) {
    console.error('导出考勤统计数据失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 更新考勤记录
// 修改考勤记录
router.put('/attendance/:id', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const attendanceId = req.params.id;
    const { status, checkInTime, remark } = req.body;
    
    // 更新考勤记录
    await pool.query(`
      UPDATE attendance
      SET status = ?, checkInTime = ?, remark = ?
      WHERE id = ?
    `, [status, checkInTime, remark, attendanceId]);
    
    // 记录操作日志 - 修复userName字段
    await pool.query(`
      INSERT INTO logs (userId, userName, userRole, action, target, targetId, content, ip)
      VALUES (?, ?, ?, 'update', 'attendance', ?, '修改考勤记录', ?)
    `, [req.user.id, req.user.name || '管理员', req.user.role, attendanceId, req.ip]);
    
    res.json({ message: '考勤记录已更新' });
  } catch (error) {
    console.error('更新考勤记录失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取考勤统计数据
router.get('/attendance/statistics', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { startDate, endDate, classId, groupBy } = req.query;
    
    // 构建查询条件
    let whereClause = '';
    const params = [];
    
    if (startDate && endDate) {
      whereClause += ' WHERE a.date BETWEEN ? AND ?';
      params.push(startDate, endDate);
    } else if (startDate) {
      whereClause += ' WHERE a.date >= ?';
      params.push(startDate);
    } else if (endDate) {
      whereClause += ' WHERE a.date <= ?';
      params.push(endDate);
    }
    
    if (classId && classId !== '0') {
      whereClause += whereClause ? ' AND a.classId = ?' : ' WHERE a.classId = ?';
      params.push(classId);
    }
    
    // 获取日期统计 - 修复 leave 关键字问题，使用反引号
    const [dailyStats] = await pool.query(`
      SELECT 
        a.date,
        COUNT(*) as total,
        SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
        SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
        SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
        SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leave\`,
        SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) as early,
        ROUND(SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as rate
      FROM attendance a
      ${whereClause}
      GROUP BY a.date
      ORDER BY a.date DESC
    `, params);
    
    // 如果请求按课程分组，则获取课程统计数据
    let courseStats = [];
    if (groupBy === 'course') {
      // 先检查 courses 表结构
      console.log('正在执行课程统计查询...');
      try {
        [courseStats] = await pool.query(`
          SELECT 
            c.id as courseId,
            c.name as courseName,
            COUNT(*) as total,
            SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
            SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
            SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
            SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leave\`,
            SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) as early,
            ROUND(SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as rate
          FROM attendance a
          JOIN courses c ON a.courseId = c.id
          ${whereClause}
          GROUP BY c.id, c.name
          ORDER BY c.name
        `, params);
        console.log(`课程统计查询成功，获取到 ${courseStats.length} 条记录`);
      } catch (error) {
        console.error('课程统计查询失败:', error);
        // 如果查询失败，尝试获取 courses 表结构
        try {
          const [columns] = await pool.query('SHOW COLUMNS FROM courses');
          console.log('courses 表结构:', columns.map(col => col.Field));
          
          // 尝试使用正确的字段名重新查询
          const courseNameField = columns.find(col => 
            col.Field.toLowerCase() === 'name' || 
            col.Field.toLowerCase() === 'coursename' || 
            col.Field.toLowerCase() === 'course_name'
          )?.Field || 'name';
          
          console.log(`使用字段 ${courseNameField} 重新查询`);
          
          [courseStats] = await pool.query(`
            SELECT 
              c.id as courseId,
              c.${courseNameField} as courseName,
              COUNT(*) as total,
              SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
              SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
              SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
              SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leave\`,
              SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) as early,
              ROUND(SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as rate
            FROM attendance a
            JOIN courses c ON a.courseId = c.id
            ${whereClause}
            GROUP BY c.id, c.${courseNameField}
            ORDER BY c.${courseNameField}
          `, params);
          
          console.log(`重新查询成功，获取到 ${courseStats.length} 条记录`);
        } catch (subError) {
          console.error('获取 courses 表结构失败:', subError);
          courseStats = []; // 如果查询失败，返回空数组
        }
      }
    }
    
    // 获取学生统计 - 修复 leave 关键字问题和 GROUP BY 问题
    const [studentStats] = await pool.query(`
      SELECT 
        a.studentId,
        u.name as studentName,
        u.userId as studentNumber,
        c.className,
        COUNT(*) as total,
        SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
        SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
        SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
        SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leave\`,
        SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) as early,
        ROUND(SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as rate
      FROM attendance a
      JOIN users u ON a.studentId = u.id
      LEFT JOIN classes c ON a.classId = c.id
      ${whereClause}
      GROUP BY a.studentId, u.name, u.userId, c.className
      ORDER BY c.className, u.name
    `, params);
    
    // 获取班级统计 - 修复 leave 关键字问题和 GROUP BY 问题
    const [classStats] = await pool.query(`
      SELECT 
        a.classId,
        c.className,
        COUNT(DISTINCT a.studentId) as studentCount,
        COUNT(*) as total,
        SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
        SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
        SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
        SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leave\`,
        SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) as early,
        ROUND(SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as rate
      FROM attendance a
      LEFT JOIN classes c ON a.classId = c.id
      ${whereClause}
      GROUP BY a.classId, c.className
      ORDER BY c.className
    `, params);
    
    // 获取总体统计 - 修复 leave 关键字问题
    const [overallStats] = await pool.query(`
      SELECT 
        COUNT(DISTINCT a.date) as totalDays,
        COUNT(DISTINCT a.studentId) as totalStudents,
        COUNT(*) as totalRecords,
        SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
        SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
        SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
        SUM(CASE WHEN a.status = 'leave' THEN 1 ELSE 0 END) as \`leave\`,
        SUM(CASE WHEN a.status = 'early' THEN 1 ELSE 0 END) as early
      FROM attendance a
      ${whereClause}
    `, params);
    
    // 计算出勤率和迟到率
    const overview = {
      ...overallStats[0],
      attendanceRate: overallStats[0].totalRecords > 0 
        ? ((overallStats[0].present / overallStats[0].totalRecords) * 100).toFixed(2) + '%' 
        : '0%',
      lateRate: overallStats[0].totalRecords > 0 
        ? ((overallStats[0].late / overallStats[0].totalRecords) * 100).toFixed(2) + '%' 
        : '0%'
    };
    
    // 返回所有统计数据
    res.json({
      overview,
      dailyStats,
      studentStats,
      classStats,
      courseStats // 添加课程统计数据
    });
  } catch (error) {
    console.error('获取考勤统计数据失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取用户列表
router.get('/users', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 先获取表结构，确定正确的字段名
    const [columns] = await pool.query('SHOW COLUMNS FROM users');
    console.log('users 表结构:', columns.map(col => col.Field));
    
    const { page = 1, pageSize = 20, role = '', search = '' } = req.query;
    const offset = (page - 1) * pageSize;
    
    // 根据实际表结构构建查询字段
    const availableFields = columns.map(col => col.Field);
    const requestedFields = ['id', 'userId', 'name', 'role', 'email', 'phone', 'status', 'createdAt'];
    const validFields = requestedFields.filter(field => availableFields.includes(field));
    
    console.log('有效字段:', validFields);
    
    if (validFields.length === 0) {
      return res.status(500).json({ message: '无法确定有效的用户表字段' });
    }
    
    // 构建查询
    let query = `
      SELECT ${validFields.join(', ')}
      FROM users
      WHERE 1=1
    `;
    
    const params = [];
    
    if (role && availableFields.includes('role')) {
      query += ` AND role = ?`;
      params.push(role);
    }
    
    if (search) {
      const searchFields = ['userId', 'name', 'email', 'phone'].filter(field => 
        availableFields.includes(field)
      );
      
      if (searchFields.length > 0) {
        query += ` AND (${searchFields.map(field => `${field} LIKE ?`).join(' OR ')})`;
        searchFields.forEach(() => params.push(`%${search}%`));
      }
    }
    
    // 获取总数
    const countQuery = query.replace(`SELECT ${validFields.join(', ')}`, 'SELECT COUNT(*) as total');
    const [countResult] = await pool.query(countQuery, params);
    const total = countResult[0].total;
    
    // 获取分页数据
    query += ` ORDER BY id DESC LIMIT ? OFFSET ?`;
    params.push(parseInt(pageSize), offset);
    
    console.log('最终查询:', query);
    console.log('参数:', params);
    
    const [rows] = await pool.query(query, params);
    
    res.json({
      total,
      page: parseInt(page),
      pageSize: parseInt(pageSize),
      data: rows
    });
  } catch (error) {
    console.error('获取用户列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});
// 获取单个用户详情
router.get('/users/:id', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 获取表结构
    const [columns] = await pool.query('SHOW COLUMNS FROM users');
    const availableFields = columns.map(col => col.Field);
    const requestedFields = ['id', 'userId', 'name', 'role', 'email', 'phone', 'status', 'department', 'classId', 'createdAt'];
    const validFields = requestedFields.filter(field => availableFields.includes(field));
    
    const userId = req.params.id;
    
    const [rows] = await pool.query(`
      SELECT ${validFields.join(', ')}
      FROM users
      WHERE id = ?
    `, [userId]);
    
    if (rows.length === 0) {
      return res.status(404).json({ message: '用户不存在' });
    }
    
    res.json(rows[0]);
  } catch (error) {
    console.error('获取用户详情失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 创建用户
router.post('/users', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { userId, name, role, email, phone, password, department, classId } = req.body;
    
    // 检查用户ID是否已存在
    const [existingUsers] = await pool.query('SELECT id FROM users WHERE userId = ?', [userId]);
    if (existingUsers.length > 0) {
      return res.status(400).json({ message: '用户ID已存在' });
    }
    
    // 获取表结构，确定正确的字段
    const [columns] = await pool.query('SHOW COLUMNS FROM users');
    const availableFields = columns.map(col => col.Field);
    console.log('users 表可用字段:', availableFields);
    
    // 构建插入字段和值
    const fields = [];
    const values = [];
    const placeholders = [];
    
    // 检查每个字段是否存在于表中
    if (availableFields.includes('userId')) {
      fields.push('userId');
      values.push(userId);
      placeholders.push('?');
    }
    
    if (availableFields.includes('name')) {
      fields.push('name');
      values.push(name);
      placeholders.push('?');
    }
    
    if (availableFields.includes('role')) {
      fields.push('role');
      values.push(role);
      placeholders.push('?');
    }
    
    if (availableFields.includes('email')) {
      fields.push('email');
      values.push(email);
      placeholders.push('?');
    }
    
    if (availableFields.includes('phone')) {
      fields.push('phone');
      values.push(phone);
      placeholders.push('?');
    }
    
    if (availableFields.includes('password')) {
      fields.push('password');
      values.push(password);
      placeholders.push('?');
    }
    
    if (availableFields.includes('department')) {
      fields.push('department');
      values.push(department);
      placeholders.push('?');
    }
    
    if (availableFields.includes('classId')) {
      fields.push('classId');
      values.push(classId);
      placeholders.push('?');
    }
    
    // 不再添加 status 字段，因为它不存在
    
    // 构建 SQL 查询
    const query = `
      INSERT INTO users (${fields.join(', ')})
      VALUES (${placeholders.join(', ')})
    `;
    
    console.log('创建用户 SQL:', query);
    console.log('参数:', values);
    
    // 创建用户
    const [result] = await pool.query(query, values);
    
    // 记录操作日志
    await pool.query(`
      INSERT INTO logs (userId, userName, userRole, action, target, targetId, content, ip)
      VALUES (?, ?, ?, 'create', 'user', ?, ?, ?)
    `, [req.user.id, req.user.name || '管理员', req.user.role, result.insertId, `创建用户: ${userId}`, req.ip]);
    
    res.status(201).json({ 
      message: '用户创建成功',
      id: result.insertId
    });
  } catch (error) {
    console.error('创建用户失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 更新用户
router.put('/users/:id', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const userId = req.params.id;
    const { name, role, email, phone, status, department, classId } = req.body;
    
    // 更新用户信息
    await pool.query(`
      UPDATE users
      SET name = ?, role = ?, email = ?, phone = ?, status = ?, department = ?, classId = ?
      WHERE id = ?
    `, [name, role, email, phone, status, department, classId, userId]);
    
    // 记录操作日志
    await pool.query(`
      INSERT INTO logs (userId, userName, userRole, action, target, targetId, content, ip)
      VALUES (?, ?, ?, 'update', 'user', ?, ?, ?)
    `, [req.user.id, req.user.name || '管理员', req.user.role, userId, `更新用户信息`, req.ip]);
    
    res.json({ message: '用户信息已更新' });
  } catch (error) {
    console.error('更新用户失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 重置用户密码
router.post('/users/:id/reset-password', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const userId = req.params.id;
    const { password } = req.body;
    
    // 更新密码
    await pool.query(`
      UPDATE users
      SET password = ?
      WHERE id = ?
    `, [password, userId]);
    
    // 记录操作日志
    await pool.query(`
      INSERT INTO logs (userId, userName, userRole, action, target, targetId, content, ip)
      VALUES (?, ?, ?, 'update', 'user', ?, ?, ?)
    `, [req.user.id, req.user.name || '管理员', req.user.role, userId, `重置用户密码`, req.ip]);
    
    res.json({ message: '密码已重置' });
  } catch (error) {
    console.error('重置密码失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 删除用户
router.delete('/users/:id', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const userId = req.params.id;
    
    // 获取用户信息用于日志记录
    const [userInfo] = await pool.query('SELECT userId, name FROM users WHERE id = ?', [userId]);
    if (userInfo.length === 0) {
      return res.status(404).json({ message: '用户不存在' });
    }
    
    // 删除用户
    await pool.query('DELETE FROM users WHERE id = ?', [userId]);
    
    // 记录操作日志
    await pool.query(`
      INSERT INTO logs (userId, userName, userRole, action, target, targetId, content, ip)
      VALUES (?, ?, ?, 'delete', 'user', ?, ?, ?)
    `, [req.user.id, req.user.name || '管理员', req.user.role, userId, `删除用户: ${userInfo[0].name} (${userInfo[0].userId})`, req.ip]);
    
    res.json({ message: '用户已删除' });
  } catch (error) {
    console.error('删除用户失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

module.exports = router;